package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.AttendanceNoticeJdbc;
import com.wuseguang.springcloud.dao.repository.AttendanceNoticeRepository;
import com.wuseguang.springcloud.service.AttendanceNoticeService;
import com.wuseguang.springcloud.util.PaginationUtil;
import com.wuseguang.springcloud.util.PagingTool;
import com.wuseguang.springcloud.util.Result;
import org.checkerframework.checker.units.qual.A;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 学生考勤通知 服务实现类
 * </p>
 *
 * @author
 * @since 2020-03-02
 */
@Service
public class AttendanceNoticeServiceImpl implements AttendanceNoticeService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private AttendanceNoticeRepository attendanceNoticeRepository;
    @Autowired
    private AttendanceNoticeJdbc attendanceNoticeJdbc;

    @Override
    public Result findAttendanceNotice(String[] classId, Integer indexNo, Integer rows, String schoolId, String teacherId) {
        //查询的班级
        String classid = null;
        //判断如果数组为空
        if (classId == null || classId.length == 0) {
            logger.info("班级为空查询默认的班级");
            classid = attendanceNoticeJdbc.findClassIds(schoolId, teacherId);
        } else {
            logger.info("班级不为空查询选择的班级");
            classid = org.apache.commons.lang.StringUtils.join(classId, ",");
        }

        //查询通知考勤SQL
        String sql = "select  CONCAT('考勤通知','') noticeName, CONCAT('考勤通知：',s.student_name,'同学',CASE WHEN  an.attendtype =0 THEN '病假' WHEN  an.attendtype =1 THEN '事假' WHEN an. attendtype =2 THEN '正常' WHEN  an.attendtype =3 THEN '进班迟到' WHEN  an.attendtype =4 THEN '进班缺卡' WHEN  an.attendtype =5 THEN '离班缺卡' ELSE '离班早退'  END) notice, DATE_FORMAT(an.opertime,'%Y-%m-%d') attendtime, DATE_FORMAT(an.opertime,'%H:%i') time ,c.id,c.name," +
                " CONCAT( '', CONVERT ('https://oss.yuguozhijia.online/icon/%E8%80%83%E5%8B%A4%E7%AE%A1%E7%90%86.png', CHAR ) ) icon from ( attendance_notice an " +
                ", student s )  LEFT JOIN classes c on(an.class_id=c.id) where an.student_id=s.id and t.class_id in (" + classid + ")  ORDER BY an.id DESC";
      /* String sql="SELECT c.id,c.type,c.state,a.icon,t.id publisher_id,t.student_name publisher,c.content,c.opertime,a.name title,CONCAT(t.student_name,'同学',c.content) body FROM all_notice c LEFT JOIN  application  " +
               " a ON(c.jurisdiction_id=a.id) LEFT JOIN student t ON(t.id=c.student_id)  WHERE c.bancard=1 AND c.type=4  AND c.class_id=? ";*/
        //考勤结果
        List<Map<String, Object>> list = attendanceNoticeJdbc.queryListMap(sql);
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @描述 查所有通知
     * @创建人 zm
     * @创建时间 2020/4/13
     */
    @Override
    public Result findClassNotice(String[] classId, Integer indexNo, Integer rows, String schoolId, String teacherId) {
        //查询的班级 请假
        String classid = null;

        String classIdChat = null;

        //判断如果数组为空
        if (classId == null || classId.length == 0) {
            logger.info("班级为空查询默认的班级");
            classIdChat = attendanceNoticeJdbc.findClassIds(schoolId, teacherId);
        } else {
            logger.info("班级不为空查询选择的班级");
            classIdChat = org.apache.commons.lang.StringUtils.join(classId, ",");
        }
        //0 待审核 (1第一审核通过 2通过) 3第一审核人拒绝 4销假中 5已销假 6第二审核人未通过
        int approver = -1;//第一审批人
        String sql = " SELECT GROUP_CONCAT(c.id) string FROM  school_vacation s,teacher_role r,classes c WHERE s.one_roles_id=r.role_id  AND r.type=3 " +
                "  AND s.school_id =r.school_id AND r.school_id=? AND r.pid=c.id AND c.graduation=0 AND r.teacher_id=? ";

        if (!StringUtils.isEmpty(classId)) {
            sql += " AND c.id=" + classId;
        }

        classid = attendanceNoticeJdbc.queryString(sql, schoolId, teacherId);//存在时为第一审批人

        if (StringUtils.isEmpty(classid)) {//不存在时 查第二审批人
            approver = 2;//第二审批人
            sql = " SELECT GROUP_CONCAT(c.id) string FROM  school_vacation s,teacher_role r,classes c WHERE s.two_roles_id=r.role_id  AND r.type=2 " +
                    "  AND s.school_id =r.school_id AND r.school_id=?  AND r.pid=c.grade_id AND c.graduation=0 AND r.teacher_id=? ";
            if (!StringUtils.isEmpty(classId)) {
                sql += " AND c.id=" + classId;
            }
            classid = attendanceNoticeJdbc.queryString(sql, schoolId, teacherId);//存在时为第二审批人
        } else {
            approver = 0;//第一审批人 没有第二审批人
            sql = " SELECT two_roles_id string FROM school_vacation WHERE school_id=?";
            if (!StringUtils.isEmpty(attendanceNoticeJdbc.queryString(sql, schoolId))) {//有第二审批人
                approver = 1;
            }
        }
        String s = "";

        if (approver == 0 || approver == 1) {
            s = " AND c.state IN (0,4)";
        }

        if (approver == 2) {
            s = " AND c.state IN (1)";
        }


        //查询通知考勤SQL
        //String sql = "select CONCAT('班牌通知','') noticeName, CONCAT('班牌通知:',CONVERT(n.content,char)) notice ,n.publisher, DATE_FORMAT(n.opertime ,'%Y-%m-%d') opertime, DATE_FORMAT(n.opertime ,'%H:%i') time,c.id,c.`name`,CONCAT( '', CONVERT ('https://oss.yuguozhijia.online/icon/%E9%80%9A%E7%9F%A5x.png', CHAR ) ) icon from notice n left join class_notice cn on(n.id=cn.notice_id) LEFT JOIN classes c on(cn.class_id=c.id) where cn.class_id in(" + classid + ")  ORDER BY cn.id DESC";
//        sql = "   SELECT c.class_id,e.name,c.id,c.type,a.icon,t.id publisher_id,t.student_name publisher,DATE_FORMAT(c.opertime ,'%Y-%m-%d') opertime,DATE_FORMAT(c.opertime ,'%H:%i') time,a.name noticeName,CONCAT(a.name,'：',t.student_name,'同学',c.content) notice  FROM all_notice c LEFT JOIN  application  " +
//                "    a ON(c.jurisdiction_id=a.id) LEFT JOIN student t ON(t.id=c.student_id)  LEFT JOIN classes e ON(t.class_id=e.id)  WHERE c.tapplet=1 AND c.type=4 " + s + "  AND t.class_id IN(" + classid + ") ORDER BY c.opertime DESC";

        sql = "  SELECT *FROM ( SELECT c.class_id,e.name,c.id,c.type,a.icon,t.id publisher_id,t.student_name publisher,DATE_FORMAT(c.opertime ,'%Y-%m-%d') opertime,DATE_FORMAT(c.opertime ,'%H:%i') time,a.name noticeName,CONCAT(a.name,'：',t.student_name,'同学',c.content) notice  FROM all_notice c LEFT JOIN  application  " +
                "    a ON(c.jurisdiction_id=a.id) LEFT JOIN student t ON(t.id=c.student_id)  LEFT JOIN classes e ON(t.class_id=e.id)  WHERE c.tapplet=1 AND c.type=4 " + s + "  AND t.class_id IN(" + classid + ")   UNION ALL" +
                "   SELECT s.id class_id,s.name,c.id,c.type,p.icon,t.id publisher_id,t.student_name publisher,DATE_FORMAT(c.opertime ,'%Y-%m-%d') " +
                " opertime,DATE_FORMAT(c.opertime ,'%H:%i') time,p.name noticeName, CONCAT(p.name,'：',t.student_name,'同学',c.content) notice  FROM all_notice c " +
                "     LEFT JOIN  application  p  ON(c.jurisdiction_id=p.id) " +
                "        LEFT JOIN student  t ON(t.id=c.student_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.tapplet=1 AND c.type=5 AND c.teacher_id=? AND s.id in (" + classIdChat + ") ) t ORDER BY opertime DESC,time DESC";
        //考勤结果
        List<Map<String, Object>> list = attendanceNoticeJdbc.queryListMap(sql, teacherId);
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }

    /**
     * @描述 查所有通知（家长端）
     * @创建人 zm
     * @创建时间 2020/4/13
     */
    @Override
    public Result findStudentNotice(Integer studentId) {

        //查询这个学生所在的班级
        String sql = "select class_id string from student where id = ? ";
        String classId = attendanceNoticeJdbc.queryString(sql, studentId);

        //查询通知考勤 SQL
        sql = "     SELECT name,type,noticeName,notice,icon,DATE_FORMAT(opertime ,'%Y-%m-%d') opertime, DATE_FORMAT(opertime ,'%H:%i') time  FROM    ( " +
                "     SELECT s.name,0 TYPE,'通知管理' noticeName,CONCAT('通知管理：',publisher,'教师:',CONVERT(n.content,CHAR)) notice  ,'https://oss.yuguozhijia.online/icon/%E9%80%9A%E7%9F%A5x.png' icon,n.opertime  " +
                "     FROM notice  n,class_notice  c,classes s  WHERE c.class_id=? AND c.notice_id=n.id AND n.pdisplay=1 AND s.id=c.class_id             " +
                "     UNION " +
                "     SELECT s.name,c.type,j.name noticeName, CONCAT(j.name,'：',t.name,'教师',c.content) notice,j.image icon , c.opertime FROM all_notice c  " +
                "     LEFT JOIN  jurisdiction  j ON(c.jurisdiction_id=j.id) " +
                "     LEFT JOIN teacher  t ON(t.id=c.teacher_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.papplet=1 AND c.type=1 AND c.class_id=?  " +
                "     UNION " +
                "     SELECT s.name,c.type,j.name noticeName, CONCAT(j.name,'：',t.name,'教师',c.content) notice,j.image icon , c.opertime FROM all_notice c " +
                "     LEFT JOIN  jurisdiction  j ON(c.jurisdiction_id=j.id) " +
                "     LEFT JOIN teacher  t ON(t.id=c.teacher_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.papplet=1 AND c.type=2 AND c.class_id=? " +
                "     UNION  " +
                "     SELECT s.name,c.type,j.name noticeName,CONCAT(j.name,'：',t.student_name,'同学',c.content) notice ,j.image icon,c.opertime  " +
                "     FROM all_notice c LEFT JOIN  jurisdiction  j ON(c.jurisdiction_id=j.id) LEFT JOIN student t ON(t.id=c.student_id) LEFT JOIN classes s ON(t.class_id=s.id)  " +
                "     WHERE c.papplet=1 AND c.type=3 AND t.id=?  " +
                "     UNION  " +
                "     SELECT s.name,c.type,p.name noticeName, CONCAT(p.name,'：',t.student_name,'同学',c.content) notice,p.icon , c.opertime FROM all_notice c  " +
                "     LEFT JOIN  application  p  ON(c.jurisdiction_id=p.id)  " +
                "     LEFT JOIN student  t ON(t.id=c.student_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.state IN (2,3,5,6) AND c.papplet=1 AND c.type=4 AND t.id=?" +
                "     UNION     " +
                "     SELECT s.name,c.type,p.name noticeName, CONCAT(p.name,'：',t.student_name,'同学',c.content) notice,p.icon , c.opertime FROM all_notice c " +
                "     LEFT JOIN  application  p  ON(c.jurisdiction_id=p.id) " +
                "     LEFT JOIN student  t ON(t.id=c.student_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.papplet=1 AND c.type=5 AND t.id=? " +
                "     UNION " +
                "     SELECT s.name,c.type,j.name noticeName, CONCAT(j.name,'：',t.student_name,'同学',c.content) notice,j.image icon , c.opertime FROM all_notice c " +
                "     LEFT JOIN  jurisdiction  j  ON(c.jurisdiction_id=j.id) " +
                "     LEFT JOIN student  t ON(t.id=c.student_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.papplet=1 AND c.type=6 AND t.id=? " +
                "     UNION  " +
                "     SELECT s.name,c.type,j.name noticeName, CONCAT(j.name,'：',t.student_name,c.content) notice,j.image icon, c.opertime FROM all_notice c " +
                "     LEFT JOIN  jurisdiction  j ON(c.jurisdiction_id=j.id) " +
                "     LEFT JOIN student  t ON(t.id=c.student_id) LEFT JOIN classes  s ON(s.id=c.class_id)  WHERE c.papplet=1 AND c.type=7 AND t.id=?" +
                "     UNION " +
                "     SELECT s.name,c.type,j.name noticeName,CONCAT( j.`name`, '：', te.`name`,'教师', c.content ) notice,j.image icon,c.opertime FROM all_notice c " +
                "     LEFT JOIN jurisdiction j ON ( c.jurisdiction_id = j.id ) LEFT JOIN student t ON ( t.class_id = c.class_id ) " +
                "     LEFT JOIN classes s ON ( s.id = c.class_id )  LEFT join teacher te on(te.id=c.teacher_id) WHERE c.papplet = 1  AND c.type =8  AND t.id =?  " +
                "     UNION " +
                "     SELECT s.name,c.type,j.name noticeName,CONCAT( j.`name`, '：', te.`name`, c.content ) notice,j.image icon,c.opertime FROM all_notice c " +
                "     LEFT JOIN jurisdiction j ON ( c.jurisdiction_id = j.id ) LEFT JOIN student t ON ( t.id = c.student_id ) " +
                "     LEFT JOIN classes s ON ( s.id = c.class_id )  LEFT join teacher te on(te.id=c.teacher_id) WHERE c.papplet = 1  AND c.type = 9  AND t.id =?  " +
                "  ) t ORDER BY t.opertime DESC  ";
        //班牌通知结果
        List<Map<String, Object>> list = attendanceNoticeJdbc.queryListMap(sql, classId, classId, classId, studentId, studentId, studentId, studentId, studentId, studentId, studentId);

        //返回数据
        return new Result(list);
    }

    @Override
    public Result findStudentAttendanceNotice(Integer studentId) {

        //查询通知考勤SQL
        String sql = "select CONCAT('考勤通知','') noticeName, CONCAT('考勤通知：',s.student_name,'同学',CASE WHEN  an.attendtype =0 THEN '病假' WHEN  an.attendtype =1 THEN '事假' WHEN an. attendtype =2 THEN '进班正常' WHEN  an.attendtype =3 THEN '进班迟到' WHEN  an.attendtype =4 THEN '进班缺卡' WHEN  an.attendtype =5 THEN '离班缺卡' WHEN  an.attendtype =9 THEN '离班正常' ELSE '离班早退'  END) notice, DATE_FORMAT(an.opertime,'%Y-%m-%d') attendtime, DATE_FORMAT(an.opertime,'%H:%i') time ,c.id,c.name ,CONCAT( '', CONVERT ('https://oss.yuguozhijia.online/icon/%E8%80%83%E5%8B%A4%E7%AE%A1%E7%90%86x.png', CHAR ) ) icon from  attendance_notice an " +
                "LEFT JOIN student s on(an.student_id=s.id)  LEFT JOIN classes c on(an.class_id=c.id) where an.student_id =?  ORDER BY an.id DESC";

        List<Map<String, Object>> list = attendanceNoticeJdbc.queryListMap(sql, studentId);

        //返回数据
        return new Result(list);
    }
}
